#!/usr/bin/python
#-*-coding: utf-8 -*-

"""This module connects the Daemon and the SQL Database.

Until version 3.3, the SQL Database is a MySQL server and uses the MySQLdb module.
It's possible extend it to others SQL Databases, like PostgreeSQL or SQL Server.

"""

import MySQLdb
import os

from time import localtime, strftime
from exceptions_ import ErroSQL

class ConnectSQL(object):
    """The SQL Database interface.
    
    This Class has methods to get data of jobs, project, source code, also 
    send the results back to database.
    
    In the future, will able to connect using several sql engines. 
    
    """
    
    def __init__(self, params):
        """The Class' constructor.
        
        Receives the information about the database and the deemon account for 
        this cluster usign a dict params.
        
        Util version 3.3, only access MySQL databases, but extends to other 
        sql engines is easy.
        
        Return a cursor to database, or raise an ErroSQL if not possible
        connect with the database.
        
        """
        
        conn = MySQLdb.connect(params['IP'], params['DBUser'], 
                               params['Passwd'], params['DataBase'])
        self.cur = conn.cursor()

        print 'Connected to the SQL server'
            
    def getJob(self, clusterID):
        """Verify if there are new jobs for this clusterID
        in the Interop Router queue and 
        get the job information from the Database.
        
        Returns a tuple that contains the Job's fields: JobId, ProjectId
        JobName and the datafile in the job.
        
        Also, add in the DB information about the time when the 
        operation occours.
        
        """

        query = """SELECT jobId, projectId, jobName, jobDataFile FROM jobs WHERE \
                (clusterId = %s AND jobstatus = 0) ORDER BY jobTimestampCreated \
                LIMIT 1""" 
        
        if self.cur.execute(query %(clusterID)):
            
            row = self.cur.fetchone()
        
            # Update the Job status and set the timestamp
            timestamp = strftime("%Y-%m-%d %H:%M:%S", localtime())
            self.cur.execute("""UPDATE jobs SET jobStatus=(%s) WHERE jobId=(%s)""" %('1', row[0]))
            self.cur.execute("""UPDATE jobs SET jobTimestampCaptured=(%s) WHERE jobId=(%s)""" ,(timestamp, row[0]))
            
            return row    # return only jobID, projectId, jobName and dataFile
             
        else:
            return        # no jobs now
    
    def getProject(self, projectId):
        """Get the information about the project with id equal to projectId.
        
        Returns a tuple that contains the parameters to execute the application
        and the number of processors per cluster node used for this 
        application when running at a cluster. 
        
        """
        
        query = """SELECT projectRequiredProcessors, projectParameters \
                FROM projects p WHERE p.projectId = %s""" 
        self.cur.execute(query %(projectId))
        
        row = self.cur.fetchone()
        
        return row
    
    def getSourceCode(self, projectId):
        """Get Project's Source Code from the Database."""
        
        query = """SELECT projectSourceFile FROM projects WHERE projectId = %s""" 
        self.cur.execute(query, (projectId,))
        
        return self.cur.fetchone()[0]

        
    def sendResults(self, cluster, job, status=2):
        """Send the Job's standard and error output and 
        send them to the database.
        
        Also, add in the DB information about the time 
        when the operation occours.
        
        """    
        
        path = os.path.join(cluster.shareFolder, 'project%s' %(job.projectId), 'job%s' %(job.id))
        id = job.id
        
        outputLog = errorLog = '0x0'
        
        if status == 2:
            result = file(path + os.sep + job.name + '.out', 'rb')
            outputLog = result.read()
        else:
            result = file(path + os.sep + job.name + '.err', 'rb')
            errorLog = result.read()
        
        # Aqui precisa verificar se jah nao tah com status != 1.
        timestamp = strftime("%Y-%m-%d %H:%M:%S", localtime())
                
        self.cur.execute("""UPDATE jobs SET jobStatus = (%s) WHERE jobID = (%s)""", (status, id))
        self.cur.execute("""UPDATE jobs SET jobTimestampEnded=(%s) WHERE jobId=(%s)""" ,(timestamp, id))
        self.cur.execute("""INSERT INTO results VALUES (%s,%s,%s)""",(id, outputLog, errorLog))
            
    def sendWorkLoad(self, id, idle):
       """Send the idle cores number of the cluster to database."""
       
       self.cur.execute("""UPDATE clusters SET clusterWorkLoad = (%s) WHERE clusterId = (%s)""", (idle, id))
    
    def closeConn(self):
        """Close the connection with de SQL Database."""
        self.cur.close()
